PHP MySQL Tips
Open a Connection to MySQL
Before we can access data in the MySQL database, we need to be able to connect to the server:
- Example (MySQLi Object-Oriented)
Object-Oriented usese the -> symbol!
<?php
$servername = "localhost"; // note: hostname is the same meaning
$username = "username";
$password = "password";
// may add database name here
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
PHP MySQL Update Data
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
a real test run
$servername = "localhost"; // note: hostname is the same meaning
$username = "root";
$password = "asdf1234";
// may add database name here
// Create connection
$conn = new mysqli($servername, $username, $password);
$sql = "use demo";
if ($conn->query($sql) === TRUE) {
echo "select DB successfully";
} else {
echo "Error selecting DB: " . $conn->error;
}
// Return name of current default database
if ($result = $conn -> query("SELECT DATABASE()")) {
$row = $result -> fetch_row();
echo "Default database is " . $row[0];
$result -> close();
}
// Change db to "test" db
$conn -> select_db("test");
// Return name of current default database
if ($result = $conn -> query("SELECT DATABASE()")) {
$row = $result -> fetch_row();
echo "Default database is " . $row[0];
$result -> close();
}
$conn -> close();
$hostname="localhost";
$usename= "williamkpchan";
$password="asdf1234";
$conn=new mysqli($hostname, $username, $password)
if($conn->connect_error){
die("connect failed: " . connect_error)
}
echo("connection ok!")
$sql = "use demo";
if ($conn->query($sql) === TRUE) {
echo "change database successfully";
} else {
echo "query error: " . $conn->error;
}
$conn->close();
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
Some tips
don't use mysql_* functions in new code.
They are no longer maintained and are officially deprecated.
Should check for errors:
$link = mysqli_connect("myhost","myuser","mypassw","mybd")
or die("Error " .mysqli_error($link));
changed action="register.php" to action="" when using the entire code inside the same page.
<form action="" method="POST">
Username: <input type="text" name="username">
Password: <input type="password" name="password">
Confirm Password: <input type="password" name="confirmPassword">
Email: <input type="text" name="email">
<input type="submit" name="submit" value="Register"> or <a href="login.php">Log in</a>
</form>
<?php
require('connect.php');
$username = $_POST['username'];
$password = $_POST['password'];
$confirmPassword = $_POST['confirmPassword'];
$email = $_POST['email'];
if(isset($_POST["submit"])){
if($query = mysqli_query($connect,"INSERT INTO users (`id`, `username`, `password`, `email`) VALUES ('', '".$username."', '".$password."', '".$email."')")){
echo "Success";
}else{
echo "Failure" . mysqli_error($connect);
}
}
mysql_connect($hostname,$username, $password) or die ("html>script language='JavaScript'>alert('connect error'),history.go(-1)/script>/html>");
mysql_select_db($dbname);
$query = "SELECT * FROM $usertable";
$result = mysql_query($query);
if($result){
while($row = mysql_fetch_array($result)){
$name = $row["$yourfield"];
echo "Name: ".$name."br/>";
}
}
The die() function is an alias of the exit() function.
If the first statement returns true, then the entire statement must be true therefore the second part is never executed.
$x = 5;
true or $x++;
echo $x; // 5
false or $x++;
echo $x; // 6
http://WilliamKpAcer/php creat database.php
localhost/php creat database.php
http://localhost/demo/testAjax.html
http://localhost/webscraping/index1.php
http://localhost/webscraping/explode.php
list() : Assign variables as if they were an array.
create database demo;
CREATE TABLE `empoyee_details` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL DEFAULT NULL,
`city` VARCHAR(255) NULL DEFAULT NULL,
`postcode` VARCHAR(255) NULL DEFAULT NULL,
`job_title` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;
$conn = mysqli_connect('localhost','root','','demo');
$open = fopen('employee-data.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($name,$city,$postcode,$job_title) = $explodeLine;
$qry = "insert into empoyee_details (name, city,postcode,job_title) values('".$name."','".$city."','".$postcode."','".$job_title."')";
mysqli_query($conn,$qry);
}
fclose($open);
Login sample
dbconnection.php
<?php
$con=$mysqli = new mysqli("localhost", "root", "asdf1234", "ptmsdb");
?>
<?php
session_start();
error_reporting(0);
include('includes/dbconnection.php');
if(isset($_POST['login'])) {
$adminuser=$_POST['username'];
$password=md5($_POST['password']);
$query=mysqli_query($con,"select ID from tbladmin where UserName='$adminuser' && Password='$password' ");
$ret=mysqli_fetch_array($query);
if($ret>0){
$_SESSION['ptmsaid']=$ret['ID'];
header('location:dashboard.php');
} else {
echo '<script>alert("Invalid Detail.")</script>';
}
}
?>
<form action="#" method="post" name="login">
<?php if($msg){ echo $msg; } ?><br>
Please sign in!<br>
<label for="username">User Name</label><br>
<input type="text" id="username" name="username" required="true">
<br>
<label for="password">Password</label><br>
<input type="password" id="password" name="password" required="true"><br>
<button id="form_submit" type="submit" name="login">Submit</button><br>
<a href="forgot-password.php">Forgot Password?</a><br>
</form>
Insert Data from an HTML Form
<?php
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Escape user inputs for security
$first_name = mysqli_real_escape_string($link, $_REQUEST['first_name']);
$last_name = mysqli_real_escape_string($link, $_REQUEST['last_name']);
$email = mysqli_real_escape_string($link, $_REQUEST['email']);
// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
if(mysqli_query($link, $sql)){
echo "Records added successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
<form action="insert.php" method="post">
<label for="firstName">First Name:</label>
<input type="text" name="first_name" id="firstName">
<label for="lastName">Last Name:</label>
<input type="text" name="last_name" id="lastName">
<label for="emailAddress">Email Address:</label>
<input type="text" name="email" id="emailAddress">
<input type="submit" value="Submit">
</form>
Update ticket
<?php
session_start();
include('includes/dbconnection.php');
error_reporting(0);
if (strlen($_SESSION['ptmsaid']==0)) {
header('location:logout.php');
} else{
if(isset($_POST['submit'])){
$tid=$_GET['editid'];
$tpype=$_POST['tickettype'];
$tprice=$_POST['tprice'];
$query=mysqli_query($con, "update tbltickettype set TicketType='$tpype',Price='$tprice' where ID='$tid'");
if ($query) {
echo '<script>alert("Ticket detail has been Updated.")</script>';
}
else {echo '<script>alert("Wrong. Please try again.")</script>';}
}
?>
<?php include_once('includes/sidebar.php');?>
<?php include_once('includes/header.php');?>
<?php include_once('includes/pagetitle.php');?>
<h4 class="header-title">Update Ticket</h4>
<form method="post" action="" name="">
<?php
$tid=$_GET['editid'];
$ret=mysqli_query($con,"select * from tbltickettype where ID='$tid'");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {
?>
<label>Ticket Type</label>
<input type="text" id="tickettype" name="tickettype"
value="<?php echo $row['TicketType'];?>"
required="true" readonly>
<label>Ticket Cost</label>
<input type="text" id="tprice" name="tprice"
value="<?php echo $row['Price'];?>"
required="true">
<?php } ?>
<button type="submit" name="submit">Update</button>
</form>
<?php include_once('includes/footer.php');?>
<?php } ?>
MySQL Version and Settings
Here are a few commands to help orient yourself.
To just check which version you're running, use SELECT VERSION()
SHOW VARIABLES will display a lot of useful settings. For instance, what is your default_storage_engine? What is your datadir? I have to read up on these as some of these must be critical to performance—starting with the size of the query cache. I'll start with tips from Peter Zaitsev.
The settings used to be littered in many my.ini files. Now that I've upgraded to 5.5, the my.ini files have disappeared. The main file seems to be "/etc/my.cnf". At the moment that seems to be the main version. There are also some sample config files in "/usr/share/mysql". I need to have a look at them.
Re-Setting the Auto-Increment Number
When you are testing, you can create and delete a lot of records. With auto-increment on, you can find yourself with ID fields with high values when there is not much in the database. To reset this, use:
ALTER TABLE mytable AUTO_INCREMENT = 12
Where 12 is the next number you want to use. It has to be beyond the last current number.
If you want to renumber everything in the auto-increment column (which is a different task, and one to consider carefully as if another table depends on that ID, you are toast), then just drop the column, and create it again, with autoimcrement.
Obtaining the Column Names of a mySQL table (PDO)
function all_table_column_names($dbh,$table) {
// returns all the column names of a table.
// Source: Stack
$q = $dbh->prepare("DESCRIBE ".$table);
$q->execute();
$fields = $q->fetchAll(PDO::FETCH_COLUMN);
return ($fields);
} // END all_table_column_names
Obtaining the Column Names returned by a Query (PDO)
When you build queries programmatically, you may not know what fields you've requested—but you may need the names in order to display them.
$row = $sth->fetch(PDO::FETCH_ASSOC);
if( ! $row ) { // some error
}
$col_names = array_keys($row);
Stored Procedures vs. Stored Functions
The main difference to keep in mind is that stored functions can be used in a SQL statement, just like native functions such as AVG(), whereas procedures must be called on their own, using CALL.
Default Values
For the DATE type that is non NULL, unless you specify a default value, MySQL will use '000-00-00', which is not a valid date and may affect your performance. I use '1970-01-01', which is the earliest date that works with InnoDB tables, despite what the manual says.
Counting Rows for Paging
When paging, you will probably issue a SELECT with a LIMIT. To know the total number of records, the traditional approach would be to issue a second query: SELECT COUNT(*) etc.
Instead, in your SELECT query with the LIMIT, insert the 'SQL_CALC_FOUND_ROWS' flag just after SELECT, e.g. SELECT SQL_CALC_FOUND_ROWS title FROM movies LIMIT 20. This causes MySQL to calculate the total number of rows that would have been returned without the LIMIT. Next, to retrieve the number of rows, issue a SELECT FOUND_ROWS().
Materialized Views
MySQL Views are basically stored SELECT statements. This means that every time you look at the data, you issue a SELECT. If, instead, the view is stored in a table, the fetching can be faster. This is called a "materialized view". According to the authors of High-Performance MySQL, Flexviews is an excellent implementation of materialized views. I haven't tried it yet, but it's on the to-do list.
Tricks with User-Defined Variables
In MySQL, you can assign user-defined variables, which are prefixed with an '@' character, with this syntax (among others): @a := 5;
The neat thing is that you can assign variables "inline" during a query. But if you are setting a variable in the SELECT part of the statement and testing it in the WHERE, you are headed for trouble, as variables may not get initialized when you think. Read the manual.
Showing Row Numbers
Here is a well-known trick to display row numbers.
SET @n = 0;
SELECT @n := @n+1 AS rownum, title FROM movies
Finding Dates that have an Aniversary in the Next x Days
Here is a trick I devised for selecting rows that include a date field 'whn' (perhaps a birthday, or a date of registration) that will have a birthday in the next x days (10 in the example):
SELECT whn
FROM friends
WHERE whn BETWEEN
(@p := TIMESTAMPADD(YEAR,TIMESTAMPDIFF(YEAR,CURDATE(),whn)-1,CURDATE()) )
AND ( @p + INTERVAL 10 DAY )
Single Query to Show Row Number, Toggle and Oscillator
In this query, in addition to the row number, we'll show a toggle (1,0,1,0 …) and an oscillator (1,-1,1,-1…), which can come in handy when combined with other fields.
This time, instead of executing two queries (one to initialize the variables, one using the variables), we'll execute a single query. The variables are initialized in a joined table whose only role is to hold the initial values. This is certainly not more efficient than the two-query approach, but it's fun to see. To make this work, replace "somefield" and "sometable" with a field and table name.
SELECT somefield,
(@n1:= @n1 + 1) num, (@n2 := 1 - @n2) tog, (@n3:= -@n3) osc
FROM sometable t
JOIN (SELECT @n1:=0, @n2:=0, @n3:=-1 ) inits
More about user-defined variables
High-Performance MySQL has a number of neat tricks with user-defined variables.
Using a Table of Integers
A table of integers ranging from, say, 1 to 100, or 1 to 10,000, can be useful to make MySQL perform some operations that normally require loops in procedural languages, such as building a list of 50 dates starting tomorrow:
SELECT today + INTERVAL i DAY AS mydate FROM
(SELECT CURDATE() today ) d
JOIN int100 i
LIMIT 50
Such tables (often easier to google as "auxilliary tables of numbers") have many uses such as generating sequences (as above), finding gaps or parsing strings.
Using a Table of Integers to Normalize a Field that Contains Multiple Values Separated by Commas or Dashes
Let's say you have a table called images, containing the names of jpg files, and a "tags" field that contains a number of tags separated by commas, such as "beach,sunset,frisbee". This works for a while in that you can search the tags fiels using LIKE or REGEXP, but at some stage you realize that you really need to normalize the database, and have the tags stored separately in a correspondence table. This query (a well-known trick) will give you a list of the picture ids and their individual tags, listing each picture as many times as it has a tag. Coming across this kind of beautiful little technique is the sort of small event that makes me glad to be alive.
SELECT imageid,
# Explaining the next line: to extract tag #2 (for instance), take the left of the string to the second comma (the inner SUBSTRING_INDEX),
# then take the right of the string, starting from the right, to the first comma (the outer SUBSTRING_INDEX)
SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', i), ',', -1) tags
FROM images img CROSS JOIN
( SELECT i FROM int100 ) ints
# To know the number of items in the tags field, count the commas then add 1.
# To count the commas, remove them (the REPLACE operation), and compare the length of the original and shortened strings.
WHERE i <= 1 + (LENGTH(tags) - LENGTH(REPLACE(tags, ',' , '')) )
ORDER BY imageid, tags
The int100 table is a table of integers, and its column i contains integers from 1 to 100 (way over the maximum for a number of tags that may be contained in the "tags" field).
From tere, you are a couple steps away from denormalizing by using an "INSERT INTO ... SELECT", or from making a list of unique tags by using a "DISTINCT".
To learn more, see:
Using a Numbers Table
Methods to Generate a Table of Integers
Using the Right Time Zones
The MySQL Manual has information about lots of ways to set the time zone in various circumstances (when launching MySQL, globally at run-time, at the connection level…) That's all well and good, but the allowable timezone values vary depending on whether you're running MySQL on Windows (e.g. Xampp), Unix (e.g. cPanel) and so on. For instance, on my cPanel VPS, by default you cannot use time_zone values such as 'Pacific/Auckland'. Instead, you have to specify an offset from UTC time—but of course such an offset will change with daylight saving time.
Instead of messing with all that, if your needs are simply to have one consistent time, in my view the simplest and easiest solution for a cPanel VPS is to set your server time exactly how you like it. You need to do it in two places (both in WHM).
• First, in Server Configuration / Server Time.
• Second, in PHP Configuration Editor / Advanced / date.timezone
When you're done, you can check that everything is working right by issuing a SELECT NOW() in MySQL, an an echo date("m/d/Y h:i:s a", time()); in PHP.
Is Your Query Cache Working?
At the top of this page, we discussed variables you can tweak in the server. To see your cache variables, run this:
show VARIABLES where variable_name regexp 'query_cache'
A SHOW GLOBAL STATUS will reveal a number of counters (stored in information_schema.GLOBAL_STATUS) that can tell you how your system is running. To look at the query cache in the particular, narrow down on the Qcache variables:
SHOW GLOBAL STATUS WHERE variable_name REGEXP 'Qcache'
The query cache's hit rate is Qcache_hits / (Qcache_hits + Com_select). If you feel too lazy to look up the values and punch them in your calculator, this query will do it for you.
SELECT 100*(hits/(hits+nocache)) AS hit_ratio
FROM
(SELECT VARIABLE_VALUE hits FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'QCACHE_HITS') h,
(SELECT VARIABLE_VALUE nocache FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'COM_SELECT') n
You want this to be as high as possible.
For your hits-to-inserts ratio, paste this:
SELECT hits/inserts AS hits_to_inserts
FROM
(SELECT VARIABLE_VALUE hits FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'QCACHE_HITS') h,
(SELECT VARIABLE_VALUE inserts FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'QCACHE_INSERTS') n
Again, you want this to be as high as possible.
To fully interpret these results… Well, that's the subject of a long discussion in High-Performance MySQL.
To defragment (but not "flush" in the sense of resetting) the query cache, run this:
FLUSH QUERY CACHE
Lost Tables after Xampp Upgrade on Windows: Using mysqlfrm
When I upgraded from xampp 1.8.0 to 1.8.3, I was complacent about backing up my databases because the previous upgrade had gone down so well. I copied the files in mysql\data, but lo and behold, I lost all my innodb tables, probably because of the jump from MySQL 5.5 to 5.6.
Fortunately the data for the most important tables was on the server, so restoring those was easy. But on the dev server I had lots of tables for local programs I'd built, and I didn't want to lose those. The data didn't matter, and that was a good thing because I wasn't able to get it back. But I managed to restore the structure of all these tables. Here are the steps.
First, download and install MySQL utilities. Use the Xampp console to stop MySQL. In xampp\mysql\data, locate all the frm files that are not working. Rename each TableName.frm file to bak_TableName.frm. Restart MySQL. Run MySQL utilities. For each file, paste this in the console:
mysqlfrm --server=root:@localhost --diagnostic --show-stats C:/xampp/mysql/data/yourdatabase/bak_TableName.frm > C:/xampp/mysql/data/yourdatabase/TableName.txt
This assumes you have no root password. If you do, modify to --server=root:YourPassword@localhost
For each table, a text file is generated in mysql\data\yourdatabase. Open each text file. In each file, you will find a CREATE statement. Paste it in SQLyog or phpMyAdmin. Adjust the table name, for instance changing `C:/xampp/mysql/data/yourdatabase`.`bak_TableName` to just `TableName`. Run the statement to recreate the table (without the data).
When you are done, stop MySQL, remove the bak_TableName.frm files, restart MySQL.
Warning: PDO::__construct(): MySQL server has gone away
I used to run into this bug. After reading up on it, I tweaked some of the variables in my.cnf. (For how to do this, see the section on MySQL Version and Settings
innodb_buffer_pool_size = 300M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 75M
innodb_log_buffer_size = 10M
innodb_lock_wait_timeout = 180
innodb_flush_log_at_trx_commit = 2
Note that if you try to do this, MySQL will not restart. You can change all these values except innodb_log_file_size. The reason is that to change the size of the log files, you first need to stop MySQL, move the existing log files to a backup location. Then you can edit innodb_log_file_size and restart MySQL.
Here are the steps on a typical LAMP setup:
/etc/init.d/mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/libmysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
/etc/init.d/mysql start
On XAMPP, you'll normally find the log files (ib_logfile0 and ib_logfile1) in C:\xampp\mysql\data.